/*
  FILENAME	: load_insub.sql
  Copyright (C) 2013  Testmonger
  See https://maketestdata.codeplex.com/license

  NOTES		: load the template data into some table
	input file columns must exactly match target table.
	you must select the input file tempalte outside of this proc, i.e. if you are
	  getting it dynamically from filedefinition table. this allows calling this proc straight away for debug, etc.
	clean out temp table outside of this proc.
	update filerun history outside of this proc.

  exec load_insub 'FileTemp','E:\APPS\InRoad\InTemplate\mytemplate1.csv'
  select * from FileTemp
  exec load_insub 'FileDefinition','E:\APPS\InRoad\InTemplate\myfiledef1.csv'
  select * from FileDefinition
  delete FileDefinition
*/


IF OBJECT_ID(N'load_insub') IS NOT NULL 
BEGIN
	DROP procedure load_insub
END
GO

CREATE PROCEDURE load_insub
	@intable varchar(50), -- name of target table, usually FileTemp
	@insub varchar(200) -- path and name of template file

AS
BEGIN
	DECLARE @vsql nvarchar(500)

	BEGIN
		-- load subinput template
		-- add error handling
--		IF @@rowcount > 0
		BEGIN
			SET @vsql='BULK INSERT '+@intable+' FROM '''+@insub+''' with(BATCHSIZE=1000,FIELDTERMINATOR = '','',FIRSTROW = 1)'
			EXEC sp_executesql @vsql
			select @vsql
		END		

	END

	finis:
	-- update history
	

END